﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace dayingjia.Common
{
    public static class DBHelper
    {
        public static readonly string connString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
        //执行无命令参数的SQL语句
        public static int ExecuteSql(string strSql)
        {
            int result = 0;
            using (SqlConnection con = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(strSql, con);
                con.Open();
                result = cmd.ExecuteNonQuery();
            }
            return result;
        }

        //执行带命令参数的SQL语句
        public static int ExecuteSql(string strSql, params SqlParameter[] paras)
        {
            int result = 0;
            using (SqlConnection con = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.Parameters.AddRange(paras);
                con.Open();
                result = cmd.ExecuteNonQuery();
            }
            return result;
        }

        //以事务方式执行SQL语句序列
        public static void ExecuteSqlTransaction(List<string> StrSqlList)
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlTransaction tran = con.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.Transaction = tran;
                try
                {
                    for (int i = 0; i < StrSqlList.Count; i++)
                    {
                        cmd.CommandText = StrSqlList[i];
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch (SqlException ex)
                {
                    tran.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
        }

        //执行无命令参数的SQL语句，返回结果集中第1行第1列的值
        public static object GetScalar(string strSql)
        {
            object result = null;
            using (SqlConnection con = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(strSql, con);
                con.Open();
                result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
            }
            return result;
        }

        //执行带命令参数的SQL语句，返回结果集中第1行第1列的值
        public static object GetScalar(string strSql, params SqlParameter[] paras)
        {
            object result = null;
            using (SqlConnection con = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.Parameters.AddRange(paras);
                con.Open();
                result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
            }
            return result;
        }

        //执行无命令参数的SQL语句，返回SqlDataReader
        public static SqlDataReader GetDataReader(string strSql)
        {
            SqlConnection con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(strSql, con);
            con.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        //执行带命令参数的SQL语句，返回SqlDataReader
        public static SqlDataReader GetReader(string strSql, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(strSql, con);
            cmd.Parameters.AddRange(paras);
            con.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        //执行无命令参数的SQL语句，返回DataTable
        public static DataTable GetDataTable(string strSql)
        {
            DataTable table = new DataTable();
            using (SqlConnection con = new SqlConnection(connString))
            {
                SqlDataAdapter dap = new SqlDataAdapter(strSql, con);
                dap.Fill(table);
            }
            return table;
        }

        //执行带命令参数的SQL语句，返回DataTable
        public static DataTable GetDataTable(string strSql, params SqlParameter[] paras)
        {
            DataTable table = new DataTable();
            using (SqlConnection con = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.Parameters.AddRange(paras);
                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                dap.Fill(table);
                cmd.Parameters.Clear();
            }
            return table;
        }

        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        public static bool ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        public static int GetMaxID(string FieldName, string wherename, string TableName)
        {
            string strsql = "select top 1 " + FieldName + " from " + TableName + " where " + wherename + " order by " + FieldName + " desc";
            object obj = GetScalar(strsql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        //执行带命令参数的SQL语句，返回DataSet
        public static DataSet GetDataSet(string strSql, params SqlParameter[] paras)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.Parameters.AddRange(paras);
                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                dap.Fill(ds);
            }
            return ds;
        }

    }
}